package com.kaifamiao.jdbc.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * 1、PreparedStatement接口 继承了 Statement接口
 */
public class PreparedStatementTest {

    public static void main(String[] args) throws Exception{

        /*
        // 为 MySQL 设置参数
        String params = "?serverTimezone=Asia/Chongqing&useServerPrepStmts=true&cachePrepStmts=true";
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/kaifamiao" + params;
        String username = "kfm";
        String password = "kfm";
        */

        String driverClassName = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:kaifamiao";
        String username = "kaifamiao";
        String password = "kaifamiao";

        // 加载并注册驱动
        Class.forName( driverClassName );
        // 建立连接
        Connection conn = DriverManager.getConnection( url, username, password );

        PreparedStatement p1 = conn.prepareStatement( "DELETE FROM t_pandas WHERE id = 2001");
        System.out.println( p1 );
        System.out.println( "删除记录数: " + p1.executeUpdate() );
        p1.close(); //关闭Statement

        PreparedStatement p2 = conn.prepareStatement( "DELETE FROM t_pandas WHERE id = 2002");
        System.out.println( p2 );
        System.out.println( "删除记录数: " + p2.executeUpdate() );
        p2.close(); //关闭Statement

        String sql = "INSERT INTO t_pandas ( name, id ) VALUES ( ?, ? )";
        // 创建Statement
        // 1、将可能带有SQL参数占位符的语句发送给数据库服务器
        // 2、根据数据库服务器返回的标识符创建一个 PreparedStatement对象
        PreparedStatement ps = conn.prepareStatement( sql );
        System.out.println( ps );

        // 为SQL参数占位符的设值
        ps.setString( 1, "小娜" );
        ps.setInt( 2, 2001 );
        System.out.println( ps );
        // "执行"SQL:
        // 1、发送 SQL参数有占位符的值 和 SQL指令标识符
        // 2、接收由数据库服务器返回的执行结果
        int count = ps.executeUpdate();
        System.out.println( count );

        // 为SQL参数占位符的设值
        ps.setString( 1, "小吒" );
        ps.setInt( 2, 2002 );
        System.out.println( ps );
        // "执行"SQL:
        // 1、发送 SQL参数有占位符的值 和 SQL指令标识符
        // 2、接收由数据库服务器返回的执行结果
        count = ps.executeUpdate();
        System.out.println( count );

        ps.close(); //关闭Statement

        // 关闭连接
        conn.close();
    }

}
